package cn.com.jinhao.hotline.knowledge.service.impl;

import cn.com.jinhao.hotline.commons.vo.knowledge.*;
import cn.com.jinhao.hotline.knowledge.service.KnowledgeService;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;

import java.sql.SQLException;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * hotline-spring-cloud
 * <p>Description: </p>
 *
 * @author ShaneMew
 * @version 1.0
 * @since 2020/7/23
 */
@Service
public class KnowledgeServiceImpl implements KnowledgeService {
    @Override
    public Map<String,Object> queryKnowledge(String content, int pageSize, int pageNo) throws Exception {
        HashMap<String, Object> map = new HashMap<>();
        StringBuilder sql = new StringBuilder("select id,title,dept_dept from TB_KNOWLEDGE_INFO  where status=1 and ");
        sql.append("(");
        String columnSql = "select lower(COLUMN_NAME) as COLUMN_NAME from ALL_TAB_COLUMNS where table_name=upper('TB_KNOWLEDGE_INFO') AND COLUMN_NAME NOT IN ('ID','DEPTCODE','KNOWLEDGECODE','STATUS','AUDIT_DEPT_CODE','DEPT_DEPT','CREATOR_CODE','CREATOR','CREATE_TIME','AUDIT_DEPT_DEPT','AUDIT_PERSON_CODE','AUDIT_PERSON_NAME','CUID','CUNM','CTMP','MUID','MUNM','MTMP')";
        List<Entity> columnRs = Db.use().query(columnSql);
        var ref = new Object() {
            boolean isStart = true;
        };
        columnRs.forEach(item -> {
            if (ref.isStart) {
                sql.append(item.getStr("COLUMN_NAME")).append(" like '%").append(content).append("%'");
            }else {
                sql.append(" or ").append(item.getStr("COLUMN_NAME")).append(" like '%").append(content).append("%'");
            }
            ref.isStart = false;
        });

        sql.append(")");

        if(pageNo != 0 && pageSize != 0) {
            PageHelper.startPage(pageNo, pageSize);
            PageInfo<Entity> pageInfo = new PageInfo<>(Db.use().query(sql.toString()));
            map.put("list", pageInfo);
            map.put("total", pageInfo.getTotal());
            map.put("pageSize", pageInfo.getPageSize());
            map.put("pageNo", pageInfo.getPageNum());
        }else {
            List<Entity> dataList = Db.use().query(sql.toString());
            map.put("list", dataList);
            map.put("total", dataList.size());
        }
        return map;
    }

    @Override
    public PopMsgResponseVO popMsgKnowledge(String deptCode) {
        PopMsgResponseVO popMsgInfo = new PopMsgResponseVO();

        // 信息查询对象生成
        QueryInfo queryInfo = new QueryInfo();
        queryInfo.setPengFeedbackCount(this.getFeedbackCount(deptCode));
        queryInfo.setExpiringSoonCount(this.getExpiringSoonCount(deptCode));
        queryInfo.setSuperviseCount(this.getSuperviseCount(deptCode));
        popMsgInfo.setQueryInfo(queryInfo);

        // 信息审核对象生成
        AuditInfo auditInfo = new AuditInfo();
        auditInfo.setSuperviseCount(this.getSuperviseCountOfAuditInfo());
        auditInfo.setDelayCount(this.getDelayCountOfAuditInfo(deptCode));
        auditInfo.setFeedbackCount(this.getFeedbackCountOfAuditInfo(deptCode));
        auditInfo.setChargebackCount(this.getChargebackCountOfAuditInfo(deptCode));
        auditInfo.setCancelCount(this.getCancelCountOfAuditInfo(deptCode));
        popMsgInfo.setAuditInfo(auditInfo);
        return popMsgInfo;
    }

    @Override
    public Map<String,Object> selectKnowledgeByCondition(KnowledgeConditionRequestVO knowledgeConditionRequestVO) throws Exception {
        StringBuilder sql = new StringBuilder("select  a.title as title,a.id id,a.knowledgecode as knowledgeCode,a.id as id,a.deptcode as deptCode,a.create_time as createTime,b.dept as dept from TB_KNOWLEDGE_INFO a LEFT JOIN KN$DEPT b ON a.deptcode=b.code  where ");
        sql.append("(").append(this.getOracleSQLIn(Arrays.asList(knowledgeConditionRequestVO.getDeptCodes().split(",")), 500, "DEPTCODE", "a")).append(")");

        if (StringUtils.isNotBlank(knowledgeConditionRequestVO.getTitle())) {
            sql.append(" and a.title like '%").append(knowledgeConditionRequestVO.getTitle()).append("%'");
        }

        if (StringUtils.isNotBlank(knowledgeConditionRequestVO.getKeywords())) {
            sql.append(" and a.keywords like '%").append(knowledgeConditionRequestVO.getKeywords()).append("%'");
        }

        if (StringUtils.isNoneBlank(knowledgeConditionRequestVO.getKnowledgeCode())) {
                sql.append(" and a.knowledgecode like '%").append(knowledgeConditionRequestVO.getKnowledgeCode()).append("%'");
        }

        if (StringUtils.isNotBlank(knowledgeConditionRequestVO.getCreateTime())) {
            String[] times = knowledgeConditionRequestVO.getCreateTime().split("\\|");
            sql.append(" and a.create_time between to_date('").append(times[0]).append("','YYYY-MM-DD HH24:MI:SS') and to_date('").append(times[1]).append("','YYYY-MM-DD HH24:MI:SS')");
        }

        HashMap<String, Object> map = new HashMap<>();
        int pageNum = knowledgeConditionRequestVO.getPageNo();
        int pageSize = knowledgeConditionRequestVO.getPageSize();
        if (pageNum != 0 && pageSize != 0) {
            PageHelper.startPage(pageNum, pageSize);
            PageInfo<KnowledgeInfo> pageInfo = new PageInfo<>(Db.use().query(sql.toString(),KnowledgeInfo.class));
            map.put("list", pageInfo);
            map.put("total", pageInfo.getTotal());
            map.put("pageSize", pageInfo.getPageSize());
            map.put("pageNo", pageInfo.getPageNum());
        }else {
            List<KnowledgeInfo> dataList = Db.use().query(sql.toString(),KnowledgeInfo.class);
            map.put("list", dataList);
            map.put("total", dataList.size());
        }
        return map;
    }

    private String getOracleSQLIn(List<?> ids, int count, String field,String table_alias) {
        count = Math.min(count, 1000);
        int len = ids.size();
        int size = len % count;
        if (size == 0) {
            size = len / count;
        } else {
            size = (len / count) + 1;
        }
        StringBuilder builder = new StringBuilder();
        for (int i = 0; i < size; i++) {
            int fromIndex = i * count;
            int toIndex = Math.min(fromIndex + count, len);
            //System.out.println(ids.subList(fromIndex, toIndex));
            String productId = StringUtils.defaultIfEmpty(StringUtils.join(ids.subList(fromIndex, toIndex), "','"), "");
            if (i != 0) {
                builder.append(" or ");
            }
            builder.append(table_alias).append(".").append(field).append(" in ('").append(productId).append("')");
        }

        return StringUtils.defaultIfEmpty(builder.toString(), table_alias + "." + field + " in ('')");
    }

    private int getCancelCountOfAuditInfo(String deptCode) {
        try {
            return Db.use().queryNumber("SELECT COUNT(evas.id) cancelCount FROM BT$EVAS evas LEFT JOIN BT$EVIF evif ON evas.BT$EVIF_CODE=evif.code WHERE evif.stat=1 and evif.iscf=0 and evas.stat=1 and evas.isvs=0 and evas.isfb=0 and evas.isfs=0 and evas.retv !=1 and evas.deny=-1 and evas.st$dept_code_a=" + deptCode).intValue();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    private int getChargebackCountOfAuditInfo(String deptCode) {
        try {
            return Db.use().queryNumber("SELECT COUNT(evas.id) cancelCount FROM BT$EVAS evas LEFT JOIN BT$EVIF evif ON evas.BT$EVIF_CODE=evif.code WHERE evif.stat=1 and evas.stat=1 and evas.isvs=0 and evas.isfb=0 and evas.isfs=0 and evas.retv =-1 and evas.deny!=1 and evas.st$dept_code_a=" + deptCode).intValue();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    private int getFeedbackCountOfAuditInfo(String deptCode) {
        try {
            return Db.use().queryNumber("SELECT COUNT(evfb.id) feedbackCount FROM BT$EVFB evfb LEFT JOIN BT$EVIF evif ON evfb.BT$EVIF_CODE=evif.code LEFT JOIN BT$EVAS evas ON evfb.BT$EVAS_CODE=evas.code WHERE evif.stat = 1 and evif.iscf=0 and evas.stat=1 and evfb.stat=1 and evif.isfs=0 and evfb.isrj=0 and evif.isfs=0 and evas.retv!=1 and evas.deny!=1 and evfb.st$dept_code_c=" + deptCode + " and evfb.st$dept_code_a !=" + deptCode + " and evif.evdt > to_date('2019-01-01','yyyy-mm-dd')").intValue();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    private int getDelayCountOfAuditInfo(String deptCode) {
        try {
            return Db.use().queryNumber("SELECT COUNT(evdl.id) delayCount FROM BT$EVDL evdl LEFT JOIN BT$EVIF evif ON evdl.BT$EVIF_CODE=evif.code LEFT JOIN BT$EVAS evas ON evdl.BT$EVAS_CODE=evas.code WHERE evif.stat = 1 and evas.stat=1 and evdl.stat=1 and evif.isfs=0 and evdl.agre=-1 and evdl.st$dept_code_c=" + deptCode).intValue();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    private int getSuperviseCountOfAuditInfo() {
        try {
            return Db.use().queryNumber("SELECT COUNT(evsp.id) superviseCount FROM BT$EVSP evsp LEFT JOIN BT$EVIF evif ON evsp.BT$EVIF_CODE=evif.code WHERE evif.stat = 1 and evsp.stat=1 and bd$mgtp_code != ' ' and evif.id  is not null").intValue();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    private int getSuperviseCount(String deptCode) {
        try {
            return Db.use().queryNumber("SELECT COUNT(evsp.id) superviseCount FROM BT$EVSP evsp LEFT JOIN BT$EVIF evif ON evsp.BT$EVIF_CODE=evif.code WHERE evif.stat = 1 and evsp.stat=1 and evsp.agre = -1 and evif.isfs=0 and evsp.st$dept_code_c=" + deptCode).intValue();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    private int getExpiringSoonCount(String deptCode) {
        try {
            return Db.use().queryNumber("SELECT COUNT(evas.id) expiringSoonCount FROM BT$EVAS evas LEFT JOIN BT$EVIF evif ON evas.BT$EVIF_CODE=evif.code WHERE evif.stat=1 and evas.stat=1 and evas.deny != 1 and evas.isfb = 0 and evas.isfs=0 and evas.retv !=1 and evif.isas=1 and evas.st$dept_code_b=" + deptCode +" and evas.epdt between sysdate and sysdate+2").intValue();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    private int getFeedbackCount(String deptCode) {
        try {
            return Db.use().queryNumber("SELECT COUNT(evas.id) feedbackCount FROM BT$EVAS evas LEFT JOIN BT$EVIF evif ON evas.BT$EVIF_CODE=evif.code WHERE evif.stat=1 and evas.stat=1 and evas.deny != 1 and evas.isfb = 0 and evas.isfs=0 and evas.retv !=1 and evas.st$dept_code_b=" + deptCode).intValue();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
}
